using System;
using OfficeOpenXml;
using System.IO;
using SurveyHost.Core.DAL;
using SubSonic;
using SurveyHost.Core.Data;
using System.Data;
using System.Xml;
using System.Web;
using System.Text.RegularExpressions;

namespace SurveyHost.Web.Admin
{
    /// <summary>
    /// Codebehind for the DataRetrieval page
    /// </summary>
    public partial class DataRetrieval : System.Web.UI.Page
    {
		#region  Methods (12)

		/// <summary>
        /// Handles the Click event of the clearAll control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
        protected void clearAll_Click(object sender, EventArgs e)
        {
            ClearAnswers();
            ClearSurveys();
            ClearStatus();
            ClearStems();
            ClearMeasures();
        }
		
		/// <summary>
        /// Handles the Click event of the clearAnswers control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
        protected void clearAnswers_Click(object sender, EventArgs e)
        {
            ClearAnswers();
            ClearSurveys();
        }
		
		/// <summary>
        /// Handles the Click event of the clearMeasuresAndQs control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
        protected void clearMeasuresAndQs_Click(object sender, EventArgs e)
        {
            ClearStems();
            ClearMeasures();
        }
		
		/// <summary>
        /// Handles the Click event of the clearQuestions control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
        protected void clearQuestions_Click(object sender, EventArgs e)
        {
            ClearStems();
        }
		
		/// <summary>
        /// Handles the Click event of the clearStatus control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
        protected void clearStatus_Click(object sender, EventArgs e)
        {
            ClearStatus();
        }

        /// <summary>
        /// Handles the Click event of the download control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <remarks>The HtmlEncode is an awful hack.  I can't find much information on how to escape xml so that it is acceptable to ExcelPackage.</remarks>
        /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
		protected void download_Click(object sender, EventArgs e)
        {
            if (!IsValid) return;

            int length = 0;
            int.TryParse(timespan.Text, out length);

            MeasureCollection mc = new MeasureController().FetchAll();

            MemoryStream ms = new MemoryStream();

            ExcelWorksheet currentSheet;
            int row = 1;
            int column = 1;

            using (ExcelPackage pack = new ExcelPackage(ms))
            {
                currentSheet = pack.Workbook.Worksheets.Add("Introduction");
                currentSheet.Cell(1, 1).Value = string.Format("The current workbook was created on {0}.  Minimum survey time length was set at {1} minutes.", DateTime.Now, length);
                currentSheet.Cell(3, 1).Value = "Included measures:";
                foreach (Measure m in mc)
                    currentSheet.Cell(4 + column++, 1).Value = Server.HtmlEncode(m.MeasureName);
                
                // record data about the surveys taken
                currentSheet = pack.Workbook.Worksheets.Add("Surveys");
                currentSheet.Cell(1, 1).Value = "Survey ID";
                currentSheet.Cell(1, 2).Value = "Consent date/start time";
                currentSheet.Cell(1, 3).Value = "Survey end time";

                int idrRow = 2;
                using (IDataReader idr = Survey.FetchAll())
                {
                    while (idr.Read())
                    {
                        currentSheet.Cell(idrRow, 1).Value = idr.GetGuid(0).ToString();
                        currentSheet.Cell(idrRow, 2).Value = idr.GetDateTime(1).ToString();
                        currentSheet.Cell(idrRow, 3).Value = idr.GetDateTime(2).ToString();
                        idrRow++;
                    }
                }
                
                column = 1;
                SurveyData.TheDataTable data;
                foreach (Measure m in mc)
                {
                    currentSheet = pack.Workbook.Worksheets.Add(EncodeWorksheetName(m.MeasureName));
                    //currentSheet.Column(0).Width = 20d; fails on opening
                    data = SPs.GetData(length, m.MeasureName).GetDataSet<SurveyData>().The;
                    // bail if no data for this measure
                    if (data.Rows.Count == 0)
                        continue;
                    // headers
                    Guid id = (Guid)data.Rows[0][0];
                    currentSheet.Cell(row, column++).Value = "Survey ID";
                    foreach (SurveyData.TheRow dr in data.Rows)
                    {
                        if (id != dr.Id)
                        {
                            break; // pivot logic; each row is a column for a row grouped by id
                        }
                        currentSheet.Cell(row, column++).Value = dr.Rank.ToString();
                    }
                    id = new Guid(); // zero out guid
                    column = 1; // one based column yeech

                    foreach (SurveyData.TheRow dr in data.Rows)
                    {
                        if (id != dr.Id) // true the first time and when Id changes
                        {
                            column = 1; // one based column yeech
                            row++; // next row
                            id = dr.Id;
                            currentSheet.Cell(row, column++).Value = dr.Id.ToString();
                        }
                        currentSheet.Cell(row, column++).Value = Encode(dr.AnswerText);
                    }
                    // setup for next measure
                    row = 1; column = 1;
                }
                pack.Save();
                ms.Seek(0, SeekOrigin.Begin);
                Response.ContentType = "application/vnd.openxmlformats";
                Response.AddHeader("Content-Disposition", "attachment; filename= Data.xlsx;");
                Response.OutputStream.Write(ms.GetBuffer(), 0, (int)ms.Length);
                Response.Flush();
                Response.Close();
                Response.End();
            }
        }

        private string EncodeWorksheetName(string p)
        {
            return Regex.Replace(p, @"<|>|\*|\?|""|\||:|/|\[|]", "_",RegexOptions.Compiled);
        }

        /// <summary>
        /// Encodes the specified text for xml.  Maybe.  Goddamnit.
        /// </summary>
        /// <param name="p">The p.</param>
        /// <returns></returns>
        private string Encode(string p)
        {
            return Server.HtmlEncode(p).Replace("'", "&apos;");  // omg do I have to do this?  Isn't there a library somewhere?  I'm going to be sick.
        }
		
		/// <summary>
        /// Clears the answers.
        /// </summary>
        private void ClearAnswers()
        {
            DeleteAllRecords(Tables.Answer);
        }
		
		/// <summary>
        /// Clears the measures.
        /// </summary>
        private void ClearMeasures()
        {
            DeleteAllRecords(Tables.Measure);
        }
		
		/// <summary>
        /// Clears the status.
        /// </summary>
        private void ClearStatus()
        {
            DeleteAllRecords(Tables.UserStatus);
        }
		
		/// <summary>
        /// Clears the stems.
        /// </summary>
        private void ClearStems()
        {
            DeleteAllRecords(Tables.Stem);
        }
		
		/// <summary>
        /// Clears the surveys.
        /// </summary>
        private void ClearSurveys()
        {
            DeleteAllRecords(Tables.Survey);
        }
		
		/// <summary>
        /// Deletes all records.
        /// </summary>
        /// <param name="table">The table.</param>
        private void DeleteAllRecords(string table)
        {
            try
            {
                Query q = new Query(table);
                q.QueryType = QueryType.Delete;
                q.Execute();
                result.Text = "<br/><span style=\"color:red;\">Records deleted.  Hope you meant to do that.";
            }
            catch
            {
                result.Text = "<br/><span style=\"color:red;\">Unable to complete your request</span>";
            }
        }

        private string Escape(string value)
        {
            return value.Replace("'", "&quot;");
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            DataBind();
        }
		#endregion 

    }
}
